﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.IO;
using LumenWorks.Framework.IO.Csv;

namespace MulticastTV
{
    /// <summary>
    /// This class is responsible for importing channel list from excell format (xls)
    /// </summary>
    class ImportFromExcel
    {
        OleDbConnection conn;
        
         /// <summary>
        /// Constructor
        /// </summary>
        public ImportFromExcel(string file)
        {
            string connstr = @"Provider=Microsoft.Jet.Oledb.4.0;Data Source="+file+@";Extended Properties=""Excel 8.0;HDR=Yes;""";
            conn = new OleDbConnection(connstr);
        }

        /// <summary>
        ///Converts received excell spreadsheet to datatable and
        ///optionally converts it to atvlist import compatible datatable.
        /// </summary>
        /// <param name="dt">Atvlist datatable blueprint</param>
        /// <param name="convert">Convert to atvlist datatable</param>
        public DataTable ConvertToDatatable(DataTable dt, bool convert)
        {
            try
            {
                conn.Open();
            }
            catch 
            {
                return null;
            }
            string first = firstSpreadSheetName();
            string strSQL = "SELECT * FROM ["+ first + "]";
            OleDbCommand cmd = new OleDbCommand(strSQL, conn);
            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(ds);
            clean();

            if (convert)
            {
                // convert to atvlist datatable
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    string ip = dr["Ip"].ToString();
                    //check for valid channel number
                    string chan = dr["Channel"].ToString();
                    int test = 0;
                    bool validcast = int.TryParse(chan, out test);

                    if (ip != string.Empty && chan != string.Empty && validcast)
                    {
                        DataRow drtemp = dt.NewRow();
                        drtemp["Channel"] = dr["Channel"];
                        drtemp["Name"] = dr["Name"].ToString();
                        drtemp["Group"] = dr["Group"].ToString();
                        drtemp["Locked"] = dr["Locked"].ToString();
                        string[] audio = { dr["Language"].ToString() + "+" + ip };
                        drtemp["Ip"] = (string[])audio;
                        dt.Rows.Add(drtemp);
                    }
                }
                return dt;
            }
            else
                return ds.Tables[0];
           
        }

        public DataTable fromCsv(string strFileName, DataTable blueprint)
        {
            using (CsvReader csv =
                   new CsvReader(new StreamReader(strFileName), true))
            {
                int fieldCount = csv.FieldCount;
                string[] headers = csv.GetFieldHeaders();

                while (csv.ReadNextRecord())
                {
                    DataRow temp = blueprint.NewRow();

                    int test = 0;
                    int.TryParse(csv[0],out test);
                    temp["Channel"] = test;
                    temp["Name"] = csv[1];
                    temp["Locked"] = csv[2];
                    temp["Group"] = csv[3];

                    // server
                    if (blueprint.Columns.IndexOf("Language") != -1)
                    {
                        temp["Language"] = csv[4];
                        temp["Ip"] = csv[5];
                    }
                    else
                    { 
                        //standard
                        temp["Ip"] = new string[] { csv[4] + "+" + csv[5] };
                    }

                    blueprint.Rows.Add(temp);
                }
            }
            return blueprint;
        }

        private string firstSpreadSheetName()
        {
            try
            {
                // Get the name of the first worksheet:
                DataTable dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dbSchema == null || dbSchema.Rows.Count < 1)
                {
                    throw new Exception("Error: Could not determine the name of the first worksheet.");
                }
                string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();
                return firstSheetName;
            }
            finally
            {
                conn.Close();
            }
        }

        public void clean()
        {
            conn.Close();
        }
    }
}
